RSMS API — results reads (Azure Table Storage + JSON blobs)

Archived. Superseded historical design text (filename legacy: “DuckDB”). Normative today: rsms-results-storage-design, rsms-master-tasklist.

Note on filename: This file was previously titled “results reads with DuckDB”. The implementation no longer uses DuckDB or Parquet for RSMS scenario plume data; the chart mapping and route shapes below are retained for historical comparison only (rsms-results-storage-design is authoritative).

Status: Design for migration / implementation. Implementation plans: rsms-api-results-implementation-plan, rsms-results-table-storage-rework-plan.

---

1. Goals

Goal	Notes
Chart parity (where data exists)	Map each legacy chart to Table Storage rows ( PlumeByTime / PlumeByMile ) or JSON blobs as in the storage design.
Minimal surface	Few read-only HTTP endpoints; no ad-hoc query language from clients.
Dense grid semantics	Concentration fields are dense float32 vectors along mile index or time index; zeros are real values in the buffer (contrast with old sparse CXPLT Parquet). Consumers may still clip small values for display.
Honest gaps	Charts 2 and 5 (legacy CTPLT semantics) remain product-defined; may consume interpolated data from the 0.2 mi grid + hourly series — see backlog. Chart 3 uses edge_timeseries.json (and/or future CTPLT derived from grid).

Storage: Azure Table Storage for plume payloads; rsms-results container for edge_timeseries.json, mass_balance.json (paths unchanged: {riverbasin_id}/{scenario_id}/...). Identifier: riverbasin_id (single word).

---

2. Architecture (API)

1. Resolve scenario — Confirm riverbasin_id, scenario_id, authorization (existing FastAPI patterns).
2. Table reads — Use azure-data-tables (or equivalent) with partition/row keys from SSOT. Single-row retrieval for:
   • Fixed hour → PlumeByTime row for time_index = f(hour); decode M × float32 → JSON series { miles, concentration }[] using mile_step (0.2 mi) and index i → miles = i * mile_step (exact mapping in SSOT).
   • Fixed mile → PlumeByMile row for mile_index; decode T × float32 → { hours, concentration }[] using hourly indexing.
3. Blob reads — Download edge_timeseries.json / mass_balance.json bytes; json.loads (already deployed files may be pretty-printed; new writes minified per storage design).
4. Return JSON — Arrays of points + meta (schema_version, mile_step, miles_count, times_count, units).

Dependencies: azure-data-tables (and existing azure-storage-blob). No duckdb.

Threading: Run sync table/blob I/O in asyncio.to_thread() (or executor) so the event loop is not blocked.

---

3. Legacy chart → data source mapping

#	Chart (legacy intent)	Source (new stack)	Notes
1	Concentration Y vs distance X at fixed hour	PlumeByTime row at time_index	Decode float32[M] → paired with mile axis (0.2 mi grid).
2	Concentration Y vs time X at fixed distance	TBD / CTPLT parity	May use PlumeByMile at nearest mile index + smoothing, or interpolated series — product backlog.
3	Edge / peak vs distance	edge_timeseries.json + GET .../edge-timeseries	Precomputed series; not live SQL over INF. Edge-profile from dense grid may align with ctplt_*.parquet retirement — serve from JSON + tolerance.
4	Mass balance vs time	mass_balance.json blob	GET .../mass-balance reads JSON (or future inline table — optional). Spill quantity from scenario entity.
5	Concentration Y vs time at fixed river mile	CTPLT / backlog	Same as legacy §2 parity discussion in storage design.

---

4. Bare-minimum endpoints (REST)

Base: /riverbasins/{riverbasin_id}/scenarios/{scenario_id}/results

4.0 Axis domain metadata

Path suffix	Source	Role
GET .../cxplt/hours-domain	Metadata entity or T from scenario results	Hour slider hours_min / hours_max, optional distinct_hours.
GET .../cxplt/by-hour	PlumeByTime	See §4.1.

CTPLT-specific domain routes ( ctplt/miles-domain, ctplt/hours-domain, ctplt/by-mile ): TBD — either backed by metadata + table indices or removed until CTPLT product path is defined.

4.1 CXPLT-style — slice at one hour (chart 1)

GET .../cxplt/by-hour — hour query param (float). Map hour → time_index (SSOT: rounding / hourly grid).

Implementation: Fetch one PlumeByTime row; decode float32 buffer; emit series with miles computed from index and mile_step.

4.2 Slice at one mile (tooling / non–chart-2)

GET .../cxplt/by-mile — miles query param. Nearest mile_index from miles / mile_step.

Implementation: Fetch one PlumeByMile row; decode to hours, concentration pairs.

4.3 Mass balance (chart 4)

GET .../mass-balance — Read mass_balance.json from blob (or future Table entity). Response shape unchanged conceptually: rows, units, spill_quantity_lb from scenario merge.

4.4 Edge chart (chart 3)

GET .../edge-profile, GET .../edge-timeseries — Continue to use edge_timeseries.json (minified on write). Logic in app/results/ moves off Parquet / DuckDB when plume source is tables + JSON only.

---

5. Endpoint summary

Endpoint	Notes
GET .../cxplt/by-hour	PlumeByTime row decode.
GET .../cxplt/by-mile	PlumeByMile row decode.
GET .../mass-balance	mass_balance.json blob.
GET .../edge-timeseries, GET .../edge-profile	JSON + tolerance metadata.
Domain helpers	Table metadata or M/T/mile_step from SSOT.

---

6. Implementation notes (no DuckDB)

• Float indexing: Map UI hour / miles to integer indices with SSOT rounding rules (align with NUMBER_OF_DECIMAL_DIGITS_* where still used in parsers).
• Binary decode: numpy.frombuffer (or struct.unpack) with dtype=float32; validate length === M or T.
• JSON numbers: After decode, vectorized rounding per rsms-results-storage-design §2.4 — miles / hours stable; concentration quantized to scenario concentration_tolerance (round(x/tol)*tol).
• Security: Validate UUIDs; parameterize table keys; no string-concatenated queries.

---

7. References

Doc / code	Role
rsms-results-storage-design	Table + blob layout, 0.2 mi grid
rsms-results-table-storage-rework-plan	Migration phases
rsms-api-fastapi/app/results/	Handlers to update

---

8. Open items

1. Remove duckdb from rsms-api-fastapi requirements** when migration is complete. Done — duckdb is not a dependency for RSMS results reads.
2. Container and Table connection strings / managed identity — config review.
3. Legacy charts 2 / 5 — CTPLT or grid interpolation product decision.

Backlog: rsms-master-tasklist.

---

RSMS API — results via Azure Table Storage (float32 plume rows) and minified JSON blobs; DuckDB retired.